/** 
 * @Title: IDOption.java 
 * @Package com.zizo.autohome.socar.carid 
 * @Description: TODO
 * @author HeYan
 * @date 2017年1月18日 上午11:56:42 
 */
package com.zizo.autohome.socar.dao;

import java.sql.Connection;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.zizo.autohome.socar.bean.BInfo;
import com.zizo.autohome.socar.bean.CInfo;
import com.zizo.autohome.socar.bean.Relate;
import com.zizo.autohome.socar.bean.RelateBrand;
import com.zizo.autohome.socar.bean.RelateCar;
import com.zizo.util.ConnectionPool;
import com.zizo.util.ExcelReader;

/**
 * @ClassName: IDOption
 * @Description: TODO
 * @author HeYan
 * @date 2017年1月18日 上午11:56:42
 * 
 */
public final class IDOption {
	private String fileName;
	private ExcelReader er;
	private Map<String, Integer> typeMap;
	private Map<String, Integer> paramMap;
	private int typeMaxID;
	private Map<String, Integer> paramMaxIDMap;
	private IDDAO dao;

	/**
	 * @Title: setGlobalVar
	 * @Description: 对brand，car，model的全局变量进行重设
	 * @param type
	 * @return void
	 * @author Alexis
	 */
	public void setGlobalVar(String type) {
		/* 更新全局变量 */
		Map<String, Integer> typeMap_ = dao.getRelateType(type);
		Map<String, Integer> paramMap_ = dao.getRelateParam(type);
		int typeMaxID_ = dao.getTypeMaxID();
		Map<String, Integer> paramMaxIDMap_ = dao.getParamMaxID();
		this.typeMap = typeMap_;
		this.paramMap = paramMap_;
		this.typeMaxID = typeMaxID_;
		this.paramMaxIDMap = paramMaxIDMap_;
	}

	/**
	 * @Title: readBInfo
	 * @Description: 操作b-info电子表格
	 * @return void
	 * @author Alexis
	 */
	public void readBInfo() {
		setGlobalVar("b");
		Sheet sheet = er.getWb().getSheet("b-info");
		String[] title = er.getExcelTitle(sheet);// Sheet 标题
		for (String s : title) {
			if (!s.matches("bid|bname|blvl")) {
				if (!typeMap.containsKey(s)) {
					typeMap.put(s, typeMaxID + 1);
				}
			}
		}
		List<Row> rows = er.getExcelContent(sheet, true);// Sheet 每行记录
		int flag = 0;
		for (Row row : rows) {
			if (row == null) {
				if (flag > 4) {
					break;
				} else {
					flag++;
					continue;
				}
			}
			BInfo binfo = new BInfo();
			int lgth = title.length;
			for (int i = 0; i < lgth; i++) {
				Cell cell = row.getCell(i);
				if (cell == null) {
					System.err.println("Excel: " + fileName + " Sheet: " + sheet.getSheetName() + " Cell: ["
							+ row.getRowNum() + "," + i + "] Is #N/A\n");
				} else {
					switch (title[i]) {
					case "bid":
						binfo.setBid((int) cell.getNumericCellValue());
						break;
					case "bname":
						binfo.setBname(cell.getStringCellValue());
						break;
					case "bcountry":
						String country = cell.getStringCellValue();
						if (!paramMap.containsKey(country)) {
							if (!paramMaxIDMap.containsKey(title[i])) {
								paramMaxIDMap.put(title[i], typeMap.get(title[i]) * 10);
							}
							int maxID = paramMaxIDMap.get(title[i]);
							paramMap.put(country, maxID + 1);
							paramMaxIDMap.put(title[i], maxID + 1);
							Relate relate = new Relate(typeMap.get(title[i]), title[i], maxID + 1, country);
							dao.insertRelate(relate);
						}
						binfo.setBcountry(paramMap.get(country));
						break;
					case "blvl":
						binfo.setBlvl((int) cell.getNumericCellValue());
						break;
					default:
						break;
					}
				}
			}
			dao.insertBInfo(binfo);
		}

	}

	/**
	 * @Title: readCInfo
	 * @Description: 操作c-info电子表格
	 * @return void
	 * @author Alexis
	 */
	public void readCInfo() {
		setGlobalVar("c");
		Sheet sheet = er.getWb().getSheet("c-info");
		String[] title = er.getExcelTitle(sheet);// Sheet 标题
		for (String s : title) {
			if (!s.matches("a_bid|bid|bname|a_cid|cid|cname")) {
				if (!typeMap.containsKey(s)) {
					typeMap.put(s, typeMaxID + 1);
				}
			}
		}
		for (String s : typeMap.keySet()) {
			System.out.println(s + "  " + typeMap.get(s));
		}
		List<Row> rows = er.getExcelContent(sheet, true);// Sheet 每行记录
		Set<Integer> carSet = new HashSet<>();
		Set<Integer> brandSet = new HashSet<>();
		int index = 0;
		int flag = 0;
		for (Row row : rows) {
			if (row == null) {
				if (flag > 4) {
					break;
				} else {
					flag++;
					continue;
				}
			}
			int bid_ = 0;
			int a_bid_ = 0;
			int cid_ = 0;
			int a_cid_ = 0;
			CInfo cinfo = new CInfo();
			int lgth = title.length;
			for (int i = 0; i < lgth; i++) {
				Cell cell = row.getCell(i);
				if (cell == null) {
					System.err.println("Excel: " + fileName + " Sheet: " + sheet.getSheetName() + " Cell: ["
							+ row.getRowNum() + "," + i + "] Is #N/A\n");
				} else {
					switch (title[i]) {
					case "a_bid":
						int a_bid = (int) cell.getNumericCellValue();
						a_bid_ = a_bid;
						break;
					case "bid":
						int bid = (int) cell.getNumericCellValue();
						bid_ = bid;
						cinfo.setC_bid(bid);
						break;
					case "a_cid":
						int a_cid = (int) cell.getNumericCellValue();
						a_cid_ = a_cid;
						break;
					case "cid":
						int cid = (int) cell.getNumericCellValue();
						cid_ = cid;
						cinfo.setCid(cid);
						break;
					case "cname":
						String cname = cell.getStringCellValue();
						cinfo.setCname(cname);
						break;
					case "clvl":
						String clvl = cell.getStringCellValue();
						if (!paramMap.containsKey(clvl)) {
							if (!paramMaxIDMap.containsKey(title[i])) {
								paramMaxIDMap.put(title[i], typeMap.get(title[i]) * 10);
							}
							int maxID = paramMaxIDMap.get(title[i]);
							paramMap.put(clvl, maxID + 1);
							paramMaxIDMap.put(title[i], maxID + 1);
							Relate relate = new Relate(typeMap.get(title[i]), title[i], maxID + 1, clvl);
							dao.insertRelate(relate);
						}
						cinfo.setClvl(paramMap.get(clvl));
						break;
					case "cbt":
						String cbt = cell.getStringCellValue();
						if (!paramMap.containsKey(cbt)) {
							if (!paramMaxIDMap.containsKey(title[i])) {
								paramMaxIDMap.put(title[i], typeMap.get(title[i]) * 10);
							}
							int maxID = paramMaxIDMap.get(title[i]);
							paramMap.put(cbt, maxID + 1);
							paramMaxIDMap.put(title[i], maxID + 1);
							Relate relate = new Relate(typeMap.get(title[i]), title[i], maxID + 1, cbt);
							dao.insertRelate(relate);
						}
						cinfo.setCbt(paramMap.get(cbt));
						break;
					case "cseg":
						String cseg = cell.getStringCellValue();
						if (!paramMap.containsKey(cseg)) {
							if (!paramMaxIDMap.containsKey(title[i])) {
								paramMaxIDMap.put(title[i], typeMap.get(title[i]) * 10);
							}
							int maxID = paramMaxIDMap.get(title[i]);
							paramMap.put(cseg, maxID + 1);
							paramMaxIDMap.put(title[i], maxID + 1);
							Relate relate = new Relate(typeMap.get(title[i]), title[i], maxID + 1, cseg);
							dao.insertRelate(relate);
						}
						cinfo.setCseg(paramMap.get(cseg));
						break;
					case "clvl_sub":
						String clvl_sub = cell.getStringCellValue();
						if (!paramMap.containsKey(clvl_sub)) {
							if (!paramMaxIDMap.containsKey(title[i])) {
								paramMaxIDMap.put(title[i], typeMap.get(title[i]) * 10);
							}
							int maxID = paramMaxIDMap.get(title[i]);
							paramMap.put(clvl_sub, maxID + 1);
							paramMaxIDMap.put(title[i], maxID + 1);
							Relate relate = new Relate(typeMap.get(title[i]), title[i], maxID + 1, clvl_sub);
							dao.insertRelate(relate);
						}
						cinfo.setClvl_sub(paramMap.get(clvl_sub));
						break;
					case "cseg_sub":
						String cseg_sub = cell.getStringCellValue();
						if (!paramMap.containsKey(cseg_sub)) {
							if (!paramMaxIDMap.containsKey(title[i])) {
								paramMaxIDMap.put(title[i], typeMap.get(title[i]) * 10);
							}
							int maxID = paramMaxIDMap.get(title[i]);
							paramMap.put(cseg_sub, maxID + 1);
							paramMaxIDMap.put(title[i], maxID + 1);
							Relate relate = new Relate(typeMap.get(title[i]), title[i], maxID + 1, cseg_sub);
							dao.insertRelate(relate);
						}
						cinfo.setCseg_sub(paramMap.get(cseg_sub));
						break;
					case "coem":
						String coem = cell.getStringCellValue();
						if (!paramMap.containsKey(coem)) {
							if (!paramMaxIDMap.containsKey(title[i])) {
								paramMaxIDMap.put(title[i], typeMap.get(title[i]) * 10);
							}
							int maxID = paramMaxIDMap.get(title[i]);
							paramMap.put(coem, maxID + 1);
							paramMaxIDMap.put(title[i], maxID + 1);
							Relate relate = new Relate(typeMap.get(title[i]), title[i], maxID + 1, coem);
							dao.insertRelate(relate);
						}
						cinfo.setCtype(paramMap.get(coem));
						break;
					case "ctype":
						String ctype = cell.getStringCellValue();
						if (!paramMap.containsKey(ctype)) {
							if (!paramMaxIDMap.containsKey(title[i])) {
								paramMaxIDMap.put(title[i], typeMap.get(title[i]) * 10);
							}
							int maxID = paramMaxIDMap.get(title[i]);
							paramMap.put(ctype, maxID + 1);
							paramMaxIDMap.put(title[i], maxID + 1);
							Relate relate = new Relate(typeMap.get(title[i]), title[i], maxID + 1, ctype);
							dao.insertRelate(relate);
						}
						cinfo.setCtype(paramMap.get(ctype));
						break;
					default:
						break;
					}
				}
			}
			if (!carSet.contains(cid_)) {
				carSet.add(cid_);
				RelateCar rc = new RelateCar();
				rc.setCid(cid_);
				rc.setA_cid(a_cid_);
				dao.insertRelateCar(rc);
			}
			if (!brandSet.contains(bid_)) {
				brandSet.add(bid_);
				RelateBrand rb = new RelateBrand();
				rb.setBid(bid_);
				rb.setA_bid(a_bid_);
				dao.insertRelateBrand(rb);
			}
			dao.insertCInfo(cinfo);
			System.out.println("C-INFO Read " + index + " Row");
			index++;
		}
	}

	/**
	 * @Title: readMInfo
	 * @Description: 操作m-info电子表格
	 * @return void
	 * @author Alexis
	 */
	public void readMInfo() {

	}

	public IDOption(String fileName, IDDAO dao) {
		super();
		this.fileName = fileName;
		this.dao = dao;
		this.er = new ExcelReader(fileName);
	}

	public static void main(String[] args) {
		Connection conn = ConnectionPool.getConnection("socar_core", "localhost", "3306");
		IDDAO dao = new IDDAO(conn);
		String filePath = "C:\\Users\\Apple\\Desktop\\WorkProject\\网站图表\\车型年表-进入数据库20170117.xlsx";
		IDOption opt = new IDOption(filePath, dao);
		opt.readCInfo();
	}
}
